package dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import beans.Bill;
import beans.Bill_work;

public class Bill_workDAO extends ConnBean{
	private Connection connection = null;
	
	/*
	 *根据编号获取账单事务
	 */
	public Bill_work getBill_work(int tno) {
		Bill_work bill_work = new Bill_work();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			if (connection == null)
				System.out.println("error");
			String sql = "select * from Bill_work where bno=" + tno;
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				System.out.println("get it");
				bill_work.setBno(resultSet.getInt(1));
				bill_work.setTno(resultSet.getInt(2));
				bill_work.setState(resultSet.getInt(3));
				bill_work.setRejectReason(resultSet.getString(4));
			} else {
				System.out.println("null");
			}
			statement.close();
			connection.close();
			// return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return bill_work; 
	}
	
	
	/*
	 * 查询所有未审批的账单 
	 */
	public ArrayList<Bill_work> get_waitAddmit(){
		ArrayList<Bill_work> bill_works = new ArrayList<>();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			if (connection == null)
				System.out.println("error");
			String sql = "select * from Bill_work where state=0";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				Bill_work bill_work = new Bill_work();
				bill_work.setBno(resultSet.getInt(1));
				bill_work.setTno(resultSet.getInt(2));
				bill_work.setState(resultSet.getInt(3));
				bill_work.setRejectReason(resultSet.getString(4));
				bill_works.add(bill_work);
			}
			statement.close();
			connection.close();
			// return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return bill_works; 
	}
	
	
	public ArrayList<Bill_work> selectAll(){
		ArrayList<Bill_work> bill_works = new ArrayList<>();
		Statement statement = null;
		ResultSet resultSet = null;
		try {
			connection = getConnection();
			if (connection == null)
				System.out.println("error");
			String sql = "select * from Bill_work";
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				Bill_work bill_work = new Bill_work();
				bill_work.setBno(resultSet.getInt(1));
				bill_work.setTno(resultSet.getInt(2));
				bill_work.setState(resultSet.getInt(3));
				bill_work.setRejectReason(resultSet.getString(4));
				bill_works.add(bill_work);
			}
			statement.close();
			connection.close();
			// return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return bill_works; 
	}
	
	
	
	/*
	 * 插入账单业务
	 * 插入成功返回1,插入失败返回0
	 */
	public int insertAccount(Bill_work bill_work) {
		PreparedStatement preparedStatement = null;
		String sql = "insert into Bill values(?,?,?,?,?)";
		int result = 0;
		if (bill_work== null)
			return result;
		int number = 0;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1,bill_work.getBno() );
			preparedStatement.setInt(2,bill_work.getTno());
			preparedStatement.setInt(3,bill_work.getState());
			preparedStatement.setString(4, bill_work.getRejectReason());
			number = preparedStatement.executeUpdate();
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//System.out.println("***"+number);
		if (number > 0)
			result = 1;
		return result;
	}
}
